package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.type.CreateIncomeExpenseDetailTypeDto
import com.opennews.openplatform.familyexpenseapi.dto.income.expense.detail.type.UpdateIncomeExpenseDetailTypeDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.*
import com.opennews.openplatform.familyexpenseapi.entity.tables.records.IncomeExpenseDetailTypeRecord
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.familyexpenseapi.jooq.RecordHelper
import com.opennews.openplatform.myspringbootcore.extension.isNullOrBlankOrLiteralNull
import com.opennews.openplatform.myspringbootcore.util.convertSnakeToCamel
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.springframework.stereotype.Service

@Service
class IncomeExpenseDetailTypeService(
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
) {
    /**
     * Prepares the initial data for IncomeExpenseDetailType.
     */
    fun init(accountGroupId: String) {
        val exists = dslContextWrapper.exists(
            INCOME_EXPENSE_DETAIL_TYPE,
            INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
        )

        if (!exists) {
            val baseTypes = dslContext.selectFrom(INCOME_EXPENSE_DETAIL_BASE_TYPE).fetch()
            val records = mutableListOf<IncomeExpenseDetailTypeRecord>()

            // Prepares the default expense types based on predefined base types.
            for (baseType in baseTypes) {
                baseType.into(IncomeExpenseDetailTypeRecord::class.java).apply {
                    RecordHelper.setCommonFields(this)
                    this.accountGroupId = accountGroupId
                    records.add(this)
                }
            }

            dslContext.batchInsert(records).execute()
        }
    }

    /**
     * Queries all detail types which are associated with the provided account group.
     *
     * @param accountGroupId:    The string of the user's account group id.
     * @param incomeExpenseType: The string of income/expense type.
     * @return List of map instances containing the keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    fun queryByAccountGroup(accountGroupId: String, incomeExpenseType: String?): List<Map<String, Any>> {
        val query = dslContext
            .select(
                INCOME_EXPENSE_DETAIL_TYPE.ID.`as`("id"),
                INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.`as`("incomeExpenseType"),
                INCOME_EXPENSE_DETAIL_TYPE.NAME.`as`("name"),
                INCOME_EXPENSE_DETAIL_TYPE.TITLE.`as`("title"),
                INCOME_EXPENSE_DETAIL_TYPE.SUBTITLE.`as`("subtitle"),
                INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.`as`("analysisIncluded"),
                INCOME_EXPENSE_DETAIL_TYPE.DATE_CREATED.`as`("dateCreated"),
                INCOME_EXPENSE_DETAIL_TYPE.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(INCOME_EXPENSE_DETAIL_TYPE)
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(
                        if (incomeExpenseType.isNullOrBlankOrLiteralNull()) {
                            DSL.trueCondition()
                        } else {
                            INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType)
                        }
                    )
            )
            .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc())

        return query.fetchMaps()
    }

    /**
     * Queries all detail types which are associated with the provided account group.
     *
     * @param accountGroupId:    The string of the user's account group id.
     * @param incomeExpenseType: The string of income/expense type.
     * @return List of name strings.
     */
    fun queryNameForAnalysisByAccountGroup(accountGroupId: String, incomeExpenseType: String): List<String> {
        return dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                    .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isTrue())
            )
            .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc())
            .fetch(INCOME_EXPENSE_DETAIL_TYPE.NAME)
    }

    /**
     * Queries the input name list and makes sure they belong to the specific account group.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param names             : The string list of name.
     * @param accountGroupId    : The string of the user's account group id.
     * @return The matched name list.
     */
    fun queryAccountGroupDetailTypes(
        incomeExpenseType: String,
        names: List<String>,
        accountGroupId: String,
    ): List<String> {
        return if (names.isNotEmpty()) {
            // Queries the names that match the income/expense type and account group.
            dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                    INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                        .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.`in`(names))
                )
                .fetch(INCOME_EXPENSE_DETAIL_TYPE.NAME)
        } else {
            emptyList() // Return an empty list if the input names are empty
        }
    }

    /**
     * Checks if the detail type name is already existing with the same account id, income/expense type, and id.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @param name:              The string of detail type name.
     * @return True means the input detail type name already exists. False means NOT.
     */
    fun checkExisting(accountGroupId: String, incomeExpenseType: String, id: String?, name: String): Boolean {
        val condition = INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
            .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
            .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.equalIgnoreCase(name))
            .and(
                if (id.isNullOrBlankOrLiteralNull()) {
                    DSL.trueCondition()
                } else {
                    INCOME_EXPENSE_DETAIL_TYPE.ID.ne(id)
                }
            )

        return dslContextWrapper.exists(INCOME_EXPENSE_DETAIL_TYPE, condition)
    }

    /**
     * Queries the current max order index with provided account group id and income/expense type.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @return The int number of current max order index.
     */
    fun queryMaxOrderIndex(accountGroupId: String, incomeExpenseType: String): Int {
        val maxOrderIndex = dslContext
            .select(DSL.max(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX))
            .from(INCOME_EXPENSE_DETAIL_TYPE)
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
            )
            .fetchOneInto(Integer::class.java)

        return maxOrderIndex as? Int ?: 0
    }

    /**
     * Queries the next max order index with provided account group id and income/expense type.
     *
     * @param accountGroupId:    The string of the account group id.
     * @param incomeExpenseType: The string of the income/expense type.
     * @return The int number of next max order index.
     */
    fun queryNextMaxOrderIndex(accountGroupId: String, incomeExpenseType: String): Int {
        return queryMaxOrderIndex(accountGroupId, incomeExpenseType) + 1
    }

    /**
     * Queries all detail types which are associated with the provided account group and are not included by analysis.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param incomeExpenseType: The string of IncomeExpenseTypeConstant.
     * @return A list of maps, each containing the keys below:
     *         - name: String of detail type name.
     *         - title: String of detail type title.
     */
    fun queryAnalysisNotIncludedDetailTypes(accountGroupId: String, incomeExpenseType: String): List<Map<String, Any>> {
        return dslContext
            .select(
                INCOME_EXPENSE_DETAIL_TYPE.NAME,
                INCOME_EXPENSE_DETAIL_TYPE.TITLE,
            )
            .from(INCOME_EXPENSE_DETAIL_TYPE)
            .where(
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                    .and(INCOME_EXPENSE_DETAIL_TYPE.ANALYSIS_INCLUDED.isFalse)
            )
            .orderBy(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX.asc())
            .fetchMaps()
    }

    /**
     * Resets the detail types order based on the sequence of the provided ID list.
     *
     * @param accountGroupId: The string of user account group id.
     * @param detailTypeIds: The list of strings representing the detail type IDs.
     */
    fun resetOrder(accountGroupId: String, detailTypeIds: List<String>) {
        // Updates each order index.
        detailTypeIds.forEachIndexed { index, id ->
            dslContext.update(INCOME_EXPENSE_DETAIL_TYPE)
                .set(INCOME_EXPENSE_DETAIL_TYPE.ORDER_INDEX, index + 1)
                .where(
                    INCOME_EXPENSE_DETAIL_TYPE.ID.eq(id)
                        .and(INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .execute()
        }
    }

    /**
     * Adds new detail type for account group.
     *
     * @param data           : The CreateIncomeExpenseDetailType which contains keys with the same name as detail type properties.
     * @param accountGroupId : The string of the user account group id.
     * @return Map instance which contains keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    fun add(@Valid data: CreateIncomeExpenseDetailTypeDto, accountGroupId: String): Map<String, Any>? {
        var savedDetailType: Map<String, Any>? = null

        if (!checkExisting(accountGroupId, data.incomeExpenseType, null, data.name)) {
            // Creates new IncomeExpenseDetailType with posted data.
            val record = dslContext.newRecord(INCOME_EXPENSE_DETAIL_TYPE)
            record.from(data)
            record.name = record.name.uppercase()
            record.accountGroupId = accountGroupId
            record.orderIndex = queryNextMaxOrderIndex(accountGroupId, record.incomeExpenseType)
            record.insert()

            savedDetailType = record.intoMap()
        }

        return savedDetailType
    }

    /**
     * Updates detail type based on provided map data and its id.
     * The map keys must match detail type property names.
     * If the map item will be ignored if its value is null.
     *
     * @param data           : The UpdateIncomeExpenseDetailType which contains keys with same name as detail type properties.
     * @param accountGroupId : The string of the user account group id.
     * @return Map instance which contains keys below.
     * id: String of detail type id.
     * incomeExpenseType: String of income/expense type.
     * name: String of detail type name.
     * title: String of detail type title.
     * subtitle: String of detail type subtitle.
     * analysisIncluded: Bool to indicate if this is included for analysis.
     * dateCreated: The formatted created date.
     * lastUpdated: The formatted last updated date.
     */
    fun update(@Valid data: UpdateIncomeExpenseDetailTypeDto, accountGroupId: String): Map<String, Any>? {
        var savedDetailType: Map<String, Any>? = null

        if (!checkExisting(accountGroupId, data.incomeExpenseType, data.id, data.name) &&
            dslContextWrapper.exists(
                INCOME_EXPENSE_DETAIL_TYPE,
                INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(INCOME_EXPENSE_DETAIL_TYPE.ID.eq(data.id))
            )
        ) {
            // Updates the detail type.
            val detailType = dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                .where(INCOME_EXPENSE_DETAIL_TYPE.ID.eq(data.id))
                .fetchOne()

            val oldDetailType = detailType?.name

            detailType?.from(data)
            detailType?.name = detailType?.name?.uppercase() ?: ""
            detailType?.update()

            val newDetailType = detailType?.name

            dslContext.update(INCOME_EXPENSE_DETAIL)
                .set(INCOME_EXPENSE_DETAIL.DETAIL_TYPE, newDetailType)
                .where(
                    INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL.TYPE.eq(data.incomeExpenseType))
                        .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(oldDetailType))
                )
                .execute()

            // Convert snake case to camel case for client
            savedDetailType = detailType?.intoMap()
                ?.mapKeys { (key, _) -> convertSnakeToCamel(key) }
        }

        return savedDetailType
    }

    /**
     * Deletes the IncomeExpenseDetailTypes with provided name.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param name              : The string of the detail type name.
     * @param accountGroupId    : The string of the user account group id.
     * @return True means deleted. False means this detail type has been used and cannot be deleted.
     */
    fun delete(incomeExpenseType: String, name: String, accountGroupId: String): Boolean {
        var isDeleted = false

        // Checks if this detail type is used by income/expense.
        val isUsed = dslContextWrapper.exists(
            INCOME_EXPENSE_DETAIL,
            INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                .and(INCOME_EXPENSE_DETAIL.TYPE.eq(incomeExpenseType))
                .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.eq(name))
        )

        if (!isUsed) {
            // If this detail type is not used then deletes it.
            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL_TYPE)
                .where(
                    INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                        .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.eq(name))
                )
                .execute()

            isDeleted = true
        }

        return isDeleted
    }

    /**
     * Deletes the IncomeExpenseDetailTypes with provided list of name.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param accountGroupId    : The string of the user account group id.
     * @param names             : The string list of name.
     * @return The list of the id string which have been actually deleted.
     */
    fun delete(incomeExpenseType: String, names: List<String>, accountGroupId: String): List<String> {
        val deletedDetailTypeIds = mutableListOf<String>()

        // Queries the input id list and makes sure they belong to the specific account group.
        // This is an example shows how to secure the data by taking extra action.
        // This can avoid other account group users calling the API directly and providing these ids which they don't actually own.
        val filteredNames = queryAccountGroupDetailTypes(incomeExpenseType, names, accountGroupId).toMutableList()

        // Queries all detail type names which are used by income/expense.
        val usedNames = queryUsedDetailTypeNames(incomeExpenseType, filteredNames, accountGroupId)

        // Excludes all used detail type names.
        usedNames.forEach { filteredNames.remove(it) }

        if (filteredNames.isNotEmpty()) {
            // Queries all ids based on incomeExpenseType and names.
            // This will be returned to client for list item managing.
            deletedDetailTypeIds.addAll(
                dslContext.selectFrom(INCOME_EXPENSE_DETAIL_TYPE)
                    .where(
                        INCOME_EXPENSE_DETAIL_TYPE.ACCOUNT_GROUP_ID.eq(accountGroupId)
                            .and(INCOME_EXPENSE_DETAIL_TYPE.INCOME_EXPENSE_TYPE.eq(incomeExpenseType))
                            .and(INCOME_EXPENSE_DETAIL_TYPE.NAME.`in`(filteredNames))
                    )
                    .fetch(INCOME_EXPENSE_DETAIL_TYPE.ID)
            )

            dslContext.deleteFrom(INCOME_EXPENSE_DETAIL_TYPE)
                .where(INCOME_EXPENSE_DETAIL_TYPE.ID.`in`(deletedDetailTypeIds))
                .execute()
        }

        return deletedDetailTypeIds
    }

    /**
     * Queries which detail type is used by income/expense based on provided income/expense type and detail type name list.
     *
     * @param incomeExpenseType : The string of the income/expense type.
     * @param detailTypeNames:  The string list of detail type names.
     * @param accountGroupId:   The string of the user's account group id.
     * @return The string list of detail type name which is used by income/expense. Any unused detail type will be filtered.
     */
    private fun queryUsedDetailTypeNames(
        incomeExpenseType: String,
        detailTypeNames: List<String>,
        accountGroupId: String,
    ): List<String> {
        return if (detailTypeNames.isNotEmpty()) {
            dslContext.selectDistinct(INCOME_EXPENSE_DETAIL.DETAIL_TYPE)
                .from(INCOME_EXPENSE_DETAIL)
                .where(
                    INCOME_EXPENSE_DETAIL.ACCOUNT_GROUP_ID.eq(accountGroupId)
                        .and(INCOME_EXPENSE_DETAIL.TYPE.eq(incomeExpenseType))
                        .and(INCOME_EXPENSE_DETAIL.DETAIL_TYPE.`in`(detailTypeNames))
                )
                .fetchInto(String::class.java)
        } else {
            emptyList()
        }
    }
}